package com.jysz.certificate.utils;

import java.io.IOException;
import java.io.OutputStream;
import java.util.Collection;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.LinkedList;
import java.util.Map;
import java.util.Set;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import ognl.Ognl;
import ognl.OgnlException;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFComment;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.hssf.util.HSSFCellUtil;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.util.CellRangeAddressBase;

import com.jysz.certificate.persistence.model.cs.CheckStock;
import com.jysz.certificate.persistence.model.cs.ShowItem;
import com.jysz.certificate.persistence.model.cs.ShowRow;

/**
 * @author <a href="mailto:javaengine@outlook.com">Kaca Rich</a>
 *
 */
public class PoiUtils {
	
    /**
     * 这是一个通用的方法，利用了OGNL表达式，可以将放置在JAVA集合中并且符号一定条件的数据以EXCEL 的形式输出到指定IO设备上
     * 
     * @param title
     *            表格标题名
     * @param headers
     *            表格属性列名数组
     * @param columns
     *            表格属性列名数组  实体列属性名          
     * @param dataset
     *            需要显示的数据集合,集合中一定要放置符合javabean风格的类的对象。此方法支持的
     *            javabean属性的数据类型有基本数据类型及String,Date,byte[](图片数据)
     * @param out
     *            与输出设备关联的流对象，可以将EXCEL文档导出到本地文件或者网络中
     * @param pattern
     *            如果有时间数据，设定输出格式。默认为"yyy-MM-dd"
     * @throws OgnlException 
     */
	  @SuppressWarnings({ "deprecation", "rawtypes" })
	public static void exportExcel(String title, String[] headers, String[] columns,
	        Collection dataset, OutputStream out, String pattern,  Map<String , Object> context) throws OgnlException {
			HSSFWorkbook workbook = new HSSFWorkbook();
	        HSSFSheet sheet = PoiUtils.createSheet(title, workbook);
	      
	        // 生成一个样式
	        HSSFCellStyle headerStyle = PoiUtils.createHeaderStyle(workbook);

	        HSSFCellStyle contentStyle = PoiUtils.createContentCellStyle(workbook);
//	        声明一个画图的顶级管理器,图片数据。。byte数组
//	        HSSFPatriarch patriarch = PoiUtils.createPatriarch(sheet);
//	        patriarch.createPicture(anchor, workbook.addPicture(
//	                bsValue, HSSFWorkbook.PICTURE_TYPE_JPEG));
	        // 产生表格标题行
	        HSSFRow row = sheet.createRow(0);
	        for (short i = 0; i < headers.length; i++) {
	            HSSFCell cell = row.createCell(i);
	            cell.setCellStyle(headerStyle);
	            HSSFRichTextString text = new HSSFRichTextString(headers[i]);
	            cell.setCellValue(text);
	        }
	        
			if (dataset != null && dataset.size() > 0) {
				int index = 0;
				for (Iterator iterator = dataset.iterator(); iterator
						.hasNext();) {
					index ++ ;
					Object cf = iterator.next();
					row = sheet.createRow(index);
					
					 for (short i = 0; i < columns.length; i++) {
						 HSSFCell cell = row.createCell(i);
			             cell.setCellStyle(contentStyle);
			             
						 Object obj = Ognl.getValue(columns[i], context, cf);
						 
						 String textValue = obj != null ? obj.toString() : "";
			             if (textValue != null) {
		                      Pattern p = Pattern.compile("^//d+(//.//d+)?{1}");
		                      Matcher matcher = p.matcher(textValue);
		                      if (matcher.matches()) {
		                          // 是数字当作double处理
		                          cell.setCellValue(Double.parseDouble(textValue));
		                      } else {
		                          HSSFRichTextString richString = new HSSFRichTextString(
		                                    textValue);
		                            HSSFFont font3 = PoiUtils.createTextFont(workbook);
		                            richString.applyFont(font3);
		                            cell.setCellValue(richString);
		                      }
		                     
			             }
					 }

				}

				try {
					workbook.write(out);
					out.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}

	    }

	  @SuppressWarnings({ "deprecation", "rawtypes" })
		public static void exportExcel(String title, String[] headers, String[] columns,
		        Collection dataset, OutputStream out, String pattern,  Map<String , Object> context,String var) throws OgnlException {
				HSSFWorkbook workbook = new HSSFWorkbook();
		        HSSFSheet sheet = PoiUtils.createSheet(title, workbook);
		      
		        // 生成一个样式
		        HSSFCellStyle headerStyle = PoiUtils.createHeaderStyle(workbook);

		        HSSFCellStyle contentStyle = PoiUtils.createContentCellStyle(workbook);
//		        声明一个画图的顶级管理器,图片数据。。byte数组
//		        HSSFPatriarch patriarch = PoiUtils.createPatriarch(sheet);
//		        patriarch.createPicture(anchor, workbook.addPicture(
//		                bsValue, HSSFWorkbook.PICTURE_TYPE_JPEG));
		        // 产生表格标题行
		        HSSFRow row = sheet.createRow(0);
		        for (short i = 0; i < headers.length; i++) {
		            HSSFCell cell = row.createCell(i);
		            cell.setCellStyle(headerStyle);
		            HSSFRichTextString text = new HSSFRichTextString(headers[i]);
		            cell.setCellValue(text);
		        }
		        
				if (dataset != null && dataset.size() > 0) {
					int index = 0;
					for (Iterator iterator = dataset.iterator(); iterator
							.hasNext();) {
						index ++ ;
						Object cf = iterator.next();
						row = sheet.createRow(index);
						context.put("index", index);
						context.put(var, cf);
						 for (short i = 0; i < columns.length; i++) {
							 HSSFCell cell = row.createCell(i);
				             cell.setCellStyle(contentStyle);
				             
							 Object obj = Ognl.getValue(columns[i], context, cf);
							 String textValue = obj != null ? obj.toString() : "";
				             if (textValue != null) {
			                      Pattern p = Pattern.compile("^//d+(//.//d+)?{1}");
			                      Matcher matcher = p.matcher(textValue);
			                      if (matcher.matches()) {
			                          // 是数字当作double处理
			                          cell.setCellValue(Double.parseDouble(textValue));
			                      } else {
			                          HSSFRichTextString richString = new HSSFRichTextString(
			                                    textValue);
			                            HSSFFont font3 = PoiUtils.createTextFont(workbook);
			                            richString.applyFont(font3);
			                            cell.setCellValue(richString);
			                      }
			                     
				             }
						 }

					}

					try {
						workbook.write(out);
						out.close();
					} catch (IOException e) {
						e.printStackTrace();
					}
				}

		    }
	  
	  //盘库汇总 导出
	  @SuppressWarnings({ "deprecation", "rawtypes" })
	  public static void exportExcelCheckStock(String title,LinkedHashMap<String,Integer> rowOne,LinkedHashMap<String,Integer> rowTwo,LinkedHashMap<String,Integer> rowThree, String[] columns,
			  LinkedList<ShowRow> dataset, OutputStream out, String pattern,  Map<String , Object> context,String var) throws OgnlException {
		  HSSFWorkbook workbook = new HSSFWorkbook();
		  HSSFSheet sheet = PoiUtils.createSheet(title, workbook);
		  
		  // 生成一个样式
		  HSSFCellStyle headerStyle = PoiUtils.createHeaderStyle(workbook);
		  HSSFCellStyle contentStyle = PoiUtils.createContentCellStyle(workbook);
		  
		  int index=-1;
		  // 产生表格标题行
		  HSSFRow row1 = sheet.createRow(++index);
		  addCell(rowOne, row1, headerStyle, sheet,index);
		  HSSFRow row2 = sheet.createRow(++index);
		  addCell(rowTwo, row2, headerStyle, sheet,index);
		  HSSFRow row3 = sheet.createRow(++index);
		  addCell(rowThree, row3, headerStyle, sheet,index);
		  
		  if (dataset != null && dataset.size() > 0) {
			  
			  for(ShowRow showRow:dataset){
				  HSSFRow row = sheet.createRow(++index);
				  context.put("index", index-2);
				  context.put(var, showRow);
				  short i=0;
				  for (i = 0; i < columns.length; i++) {//每一列用var取
					  HSSFCell cell = row.createCell(i);
					  cell.setCellStyle(contentStyle);
					  
					  Object obj = Ognl.getValue(columns[i], context, showRow);
					  String textValue = obj != null ? obj.toString() : "";
					  if (textValue != null) {
						  Pattern p = Pattern.compile("^//d+(//.//d+)?{1}");
						  Matcher matcher = p.matcher(textValue);
						  if (matcher.matches()) {
							  // 是数字当作double处理
							  cell.setCellValue(Double.parseDouble(textValue));
						  } else {
							  HSSFRichTextString richString = new HSSFRichTextString(
									  textValue);
							  HSSFFont font3 = PoiUtils.createTextFont(workbook);
							  richString.applyFont(font3);
							  cell.setCellValue(richString);
						  }
						  
					  }
				  }
				  //
				  LinkedList<ShowItem> itemList=showRow.getItemList();
				  for(ShowItem item :itemList){
					  LinkedList<String> bankNums=item.getNumberList();
					  for(String textValue:bankNums){
						  
						  HSSFCell cell = row.createCell(i);
						  i++;
						  cell.setCellStyle(contentStyle);
						  addCell(textValue, cell, workbook);
					  }
					  
					  String csway;
					  if(CommonUtil.isEmpty(item.getCsway()))
						  csway="";
					  else if(item.getCsway().equals("0"))
						  csway="非现场";
					  else
						  csway="现场";
					  HSSFCell cellCsway = row.createCell(i);
					  cellCsway.setCellStyle(contentStyle);
					  addCell(csway, cellCsway, workbook);
					  
					  i++;
					  String submitDate=item.getSubmitDate();
					  HSSFCell cellSubmitDate= row.createCell(i);
					  cellSubmitDate.setCellStyle(contentStyle);
					  addCell(submitDate, cellSubmitDate, workbook);
					  
					  i++;
					  String remark=item.getRemark();
					  HSSFCell cellRemark= row.createCell(i);
					  cellRemark.setCellStyle(contentStyle);
					  addCell(remark, cellRemark, workbook);
					  i++;
				  }
			  }
			  
			  try {
				  workbook.write(out);
				  out.close();
			  } catch (IOException e) {
				  e.printStackTrace();
			  }
		  }
		  
	  }
	  public static void addCell(LinkedHashMap<String,Integer> rowOne,HSSFRow row,HSSFCellStyle headerStyle,HSSFSheet sheet,int rowIndex){
		  short top=-1;
			 for (String key:rowOne.keySet()) {
				 
				  HSSFCell cell = row.createCell(++top);
				  int tmp=top;
				  cell.setCellStyle(headerStyle);
				  HSSFRichTextString text = new HSSFRichTextString(getTextString(key));
				  cell.setCellValue(text);
				  if(rowOne.get(key)>1){
					 for(int i=0;i<rowOne.get(key)-1;i++){
						 HSSFCell tcell = row.createCell(++top);
						  cell.setCellStyle(headerStyle);
						  HSSFRichTextString ttext = new HSSFRichTextString(i==0?getTextString(key):"");
						  tcell.setCellValue(ttext);
					 }
					 int columnTo=tmp+rowOne.get(key)-1;
					 sheet.addMergedRegion(new CellRangeAddress(rowIndex, 
							 rowIndex,tmp,columnTo));//起始行,跨几行,起始列,跨几列.   (不夸列为0.)
					//设置合并以后的单元格的样式         
					 HSSFRow row_temp = row;             
					 for(int j=tmp;j<=columnTo;j++){                 
						 HSSFCell cell_temp = row_temp.getCell(j);                 
						 if(cell_temp ==null ){                     
							 cell_temp = row_temp.createCell(j);                 
						}                 
						cell_temp.setCellStyle(headerStyle);         
					}
				}
				 
			  }
	  }
	  
	  //导出盘库详情
	  @SuppressWarnings({ "deprecation", "rawtypes" })
	  public static void exportExcelCheckStockView(CheckStock cs,String title,OutputStream out,String[] columns,String[] headers,
		        Collection dataset, String pattern,  Map<String , Object> context,String var) throws OgnlException {
		  HSSFWorkbook workbook = new HSSFWorkbook();
		  HSSFSheet sheet = PoiUtils.createSheet(title, workbook);
		  
		  // 生成一个样式
		  HSSFCellStyle headerStyle = PoiUtils.createHeaderStyle(workbook);
		  HSSFCellStyle contentStyle = PoiUtils.createContentCellStyle(workbook);
		  addCheckStockViewHeader(cs,dataset!=null?dataset.size():0,headerStyle,contentStyle, sheet);


		// 产生表格标题行
	        HSSFRow row = sheet.createRow(6);
	        for (short i = 0; i < headers.length; i++) {
	            HSSFCell cell = row.createCell(i);
	            cell.setCellStyle(headerStyle);
	            HSSFRichTextString text = new HSSFRichTextString(headers[i]);
	            cell.setCellValue(text);
	        }
		  
		  if (dataset != null && dataset.size() > 0) {
				int index = 6;
				for (Iterator iterator = dataset.iterator(); iterator
						.hasNext();) {
					index ++ ;
					Object cf = iterator.next();
					row = sheet.createRow(index);
					context.put("index", index-6);
					 for (short i = 0; i < columns.length; i++) {
						 HSSFCell cell = row.createCell(i);
			             cell.setCellStyle(contentStyle);
			             
						 Object obj = Ognl.getValue(columns[i], context, cf);
						 
						 String textValue = obj != null ? obj.toString() : "";
			             if (textValue != null) {
		                      Pattern p = Pattern.compile("^//d+(//.//d+)?{1}");
		                      Matcher matcher = p.matcher(textValue);
		                      if (matcher.matches()) {
		                          // 是数字当作double处理
		                          cell.setCellValue(Double.parseDouble(textValue));
		                      } else {
		                          HSSFRichTextString richString = new HSSFRichTextString(
		                                    textValue);
		                            HSSFFont font3 = PoiUtils.createTextFont(workbook);
		                            richString.applyFont(font3);
		                            cell.setCellValue(richString);
		                      }
		                     
			             }
					 }

				}
			}
		 
		 try {
			  workbook.write(out);
			  out.close();
		  } catch (IOException e) {
			  e.printStackTrace();
		  }
		  
	  }
	  
	  public static void addCheckStockViewHeader(CheckStock cs,int count,HSSFCellStyle headerStyle,HSSFCellStyle contentStyle,HSSFSheet sheet){
		  int index=0;
		  HSSFRow row = sheet.createRow(index);
		  HSSFCell cell=row.createCell(0);
		  cell.setCellStyle(headerStyle);
		  HSSFRichTextString text = new HSSFRichTextString("区域经理");
		  cell.setCellValue(text);
		  
		  cell=row.createCell(1);
		  cell.setCellStyle(contentStyle);
		  text = new HSSFRichTextString(cs.getAreaManager().getRealName());
		  cell.setCellValue(text);
		  
		  cell=row.createCell(2);
		  cell.setCellStyle(headerStyle);
		  text = new HSSFRichTextString("店号");
		  cell.setCellValue(text);
		  
		  cell=row.createCell(3);
		  cell.setCellStyle(contentStyle);
		  text = new HSSFRichTextString(cs.getShop().getShopMark());
		  cell.setCellValue(text);
		  
		  cell=row.createCell(4);
		  cell.setCellStyle(headerStyle);
		  text = new HSSFRichTextString("驻店顾问");
		  cell.setCellValue(text);
		  
		  cell=row.createCell(5);
		  cell.setCellStyle(contentStyle);
		  text = new HSSFRichTextString(cs.getShopUser().getRealName());
		  cell.setCellValue(text);
		  //---第二行
		  row = sheet.createRow(++index);
		  cell=row.createCell(0);
		  cell.setCellStyle(headerStyle);
		  text = new HSSFRichTextString("应在库合格证");
		  cell.setCellValue(text);
		  
		  cell=row.createCell(1);
		  cell.setCellStyle(contentStyle);
		  text = new HSSFRichTextString(count+"");
		  cell.setCellValue(text);
		  
		  cell=row.createCell(2);
		  cell.setCellStyle(headerStyle);
		  text = new HSSFRichTextString("应在库车");
		  cell.setCellValue(text);
		  
		  cell=row.createCell(3);
		  cell.setCellStyle(contentStyle);
		  text = new HSSFRichTextString(count+"");
		  cell.setCellValue(text);
		  
		  cell=row.createCell(4);
		  cell.setCellStyle(headerStyle);
		  text = new HSSFRichTextString("店名");
		  cell.setCellValue(text);
		  
		  cell=row.createCell(5);
		  cell.setCellStyle(contentStyle);
		  text = new HSSFRichTextString(cs.getShop().getShopName());
		  cell.setCellValue(text);
		  //---第三行
		  row = sheet.createRow(++index);
		  cell=row.createCell(0);
		  cell.setCellStyle(headerStyle);
		  text = new HSSFRichTextString("盘库方式");
		  cell.setCellValue(text);
		  
		  cell=row.createCell(1);
		  cell.setCellStyle(contentStyle);
		  text = new HSSFRichTextString(cs.getLive()?"现场":"非现场");
		  cell.setCellValue(text);
		  
		  cell=row.createCell(2);
		  cell.setCellStyle(headerStyle);
		  text = new HSSFRichTextString("盘库来源");
		  cell.setCellValue(text);
		  
		  cell=row.createCell(3);
		  cell.setCellStyle(contentStyle);
		  text = new HSSFRichTextString(cs.getSource().getName());
		  cell.setCellValue(text);
		  
		  cell=row.createCell(4);
		  cell.setCellStyle(headerStyle);
		  text = new HSSFRichTextString("盘库人");
		  cell.setCellValue(text);
		  
		  cell=row.createCell(5);
		  cell.setCellStyle(contentStyle);
		  text = new HSSFRichTextString(cs.getSponsor().getRealName());
		  cell.setCellValue(text);
		  
		  //---第四行
		  row = sheet.createRow(++index);
		  cell=row.createCell(0);
		  cell.setCellStyle(headerStyle);
		  text = new HSSFRichTextString("盘库结果");
		  cell.setCellValue(text);
		  
		  cell=row.createCell(1);
		  cell.setCellStyle(contentStyle);
		  if(cs.getSuccess()==null)
			  text = new HSSFRichTextString("未结束");
		  else
			  text = new HSSFRichTextString(cs.getSuccess()?"成功":"失败");
		  cell.setCellValue(text);
		  
		  
		  cell=row.createCell(2);
		  cell.setCellStyle(headerStyle);
		  text = new HSSFRichTextString("结果备注");
		  cell.setCellValue(text);
		  
		  cell=row.createCell(3);
		  cell.setCellStyle(contentStyle);
		  text = new HSSFRichTextString(cs.getResultRemark());
		  cell.setCellValue(text);
		  
		  sheet.addMergedRegion(new CellRangeAddress(3, 
					 3,3,5));//起始行,跨几行,起始列,跨几列.   (不夸列为0.)
			//设置合并以后的单元格的样式         
			 HSSFRow row_temp = row;             
			 for(int j=3;j<=5;j++){                 
				 HSSFCell cell_temp = row_temp.getCell(j);                 
				 if(cell_temp ==null ){                     
					 cell_temp = row_temp.createCell(j);                 
				}                 
				cell_temp.setCellStyle(contentStyle);         
			}
			 //---第五行
			 row = sheet.createRow(++index);
			 cell=row.createCell(0);
			 cell.setCellStyle(headerStyle);
			 text = new HSSFRichTextString("备注");
			 cell.setCellValue(text);
			 
			 cell=row.createCell(1);
			 cell.setCellStyle(contentStyle);
			 text = new HSSFRichTextString(cs.getRemark());
			 cell.setCellValue(text);
			 
			 sheet.addMergedRegion(new CellRangeAddress(4, 
					 4,1,5));//起始行,跨几行,起始列,跨几列.   (不夸列为0.)
			 //设置合并以后的单元格的样式         
			 row_temp = row;             
			 for(int j=1;j<=5;j++){                 
				 HSSFCell cell_temp = row_temp.getCell(j);                 
				 if(cell_temp ==null ){                     
					 cell_temp = row_temp.createCell(j);                 
				 }                 
				 cell_temp.setCellStyle(contentStyle);         
			 }
		  
		 /* short top=-1;
			 for (String key:rowOne.keySet()) {
				 
				  HSSFCell cell = row.createCell(++top);
				  int tmp=top;
				  cell.setCellStyle(headerStyle);
				  HSSFRichTextString text = new HSSFRichTextString(getTextString(key));
				  cell.setCellValue(text);
				  if(rowOne.get(key)>1){
					 for(int i=0;i<rowOne.get(key)-1;i++){
						 HSSFCell tcell = row.createCell(++top);
						  cell.setCellStyle(headerStyle);
						  HSSFRichTextString ttext = new HSSFRichTextString(i==0?getTextString(key):"");
						  tcell.setCellValue(ttext);
					 }
					 int columnTo=tmp+rowOne.get(key)-1;
					 sheet.addMergedRegion(new CellRangeAddress(rowIndex, 
							 rowIndex,tmp,columnTo));//起始行,跨几行,起始列,跨几列.   (不夸列为0.)
					//设置合并以后的单元格的样式         
					 HSSFRow row_temp = row;             
					 for(int j=tmp;j<=columnTo;j++){                 
						 HSSFCell cell_temp = row_temp.getCell(j);                 
						 if(cell_temp ==null ){                     
							 cell_temp = row_temp.createCell(j);                 
						}                 
						cell_temp.setCellStyle(headerStyle);         
					}
				}
				 
			  }*/
	  }
	  
	  public static String  getTextString(String s){
		  if(CommonUtil.isNotEmpty(s)){
			  if(s.length()>=2&&s.indexOf("_")!=-1){
				  s=s.substring(0, s.indexOf("_"));
			  }
		  }
		  return s;
	  }
	  
	  public static void addCell(String textValue,HSSFCell cell,HSSFWorkbook workbook){
		  if (textValue != null) {
			  Pattern p = Pattern.compile("^//d+(//.//d+)?{1}");
			  Matcher matcher = p.matcher(textValue);
			  if (matcher.matches()) {
				  // 是数字当作double处理
				  cell.setCellValue(Double.parseDouble(textValue));
			  } else {
				  HSSFRichTextString richString = new HSSFRichTextString(
						  textValue);
				  HSSFFont font3 = PoiUtils.createTextFont(workbook);
				  richString.applyFont(font3);
				  cell.setCellValue(richString);
			  }
			  
		  }
		  
	  }
	@SuppressWarnings("deprecation")
	public static HSSFSheet createSheet(String title, HSSFWorkbook workbook) {
		// 生成一个表格
		HSSFSheet sheet = workbook.createSheet(title);
		// 设置表格默认列宽度为15个字节
		sheet.setDefaultColumnWidth((short) 15);
		return sheet;
	}

	public static HSSFPatriarch createPatriarch(HSSFSheet sheet) {
		HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
		// 定义注释的大小和位置,详见文档
		HSSFComment comment = patriarch.createComment(new HSSFClientAnchor(0,
		        0, 0, 0, (short) 4, 2, (short) 6, 5));
		// 设置注释内容
		comment.setString(new HSSFRichTextString("可以在POI中添加注释！"));
		// 设置注释作者，当鼠标移动到单元格上是可以在状态栏中看到该内容.
		comment.setAuthor("leno");
		return patriarch;
	}

	public static HSSFCellStyle createContentCellStyle(HSSFWorkbook workbook) {
		// 生成并设置另一个样式
		HSSFCellStyle style2 = workbook.createCellStyle();
//		style2.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
//		style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
		style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
		style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		// 生成另一个字体
		HSSFFont font2 = workbook.createFont();
		font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
		// 把字体应用到当前的样式
		style2.setFont(font2);
		return style2;
	}

	public static HSSFCellStyle createHeaderStyle(HSSFWorkbook workbook) {
		HSSFCellStyle style = workbook.createCellStyle();
		// 设置这些样式
		style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
		style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);
		style.setBorderTop(HSSFCellStyle.BORDER_THIN);
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		
        // 生成一个字体
        HSSFFont font = workbook.createFont();
        font.setColor(HSSFColor.VIOLET.index);
        font.setFontHeightInPoints((short) 12);
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        // 把字体应用到当前的样式
        style.setFont(font);
        
		return style;
	}
	  
	
	public static HSSFFont createTextFont(HSSFWorkbook workbook) {
		HSSFFont font3 = workbook.createFont();
		font3.setColor(HSSFColor.BLUE.index);
		return font3;
	}
	
	
	  /**
	 * @param title
	 * @param headers
	 * @param columns
	 * @param dataset
	 * @param out
	 * @param pattern
	 * @param context
	 * @param colorYellowRow 那些行需要显示为黄色
	 * @throws OgnlException
	 */
	@SuppressWarnings({ "deprecation", "rawtypes" })
	public static void exportExcel(String title, String[] headers, String[] columns,
	        Collection dataset, OutputStream out, String pattern,  Map<String , Object> context, Set<Integer> colorYellowRow) throws OgnlException {
			HSSFWorkbook workbook = new HSSFWorkbook();
	        HSSFSheet sheet = PoiUtils.createSheet(title, workbook);
	      
	        // 生成一个样式
	        HSSFCellStyle headerStyle = PoiUtils.createHeaderStyle(workbook);

	        HSSFCellStyle contentStyle = PoiUtils.createContentCellStyle(workbook);
	        
	        HSSFCellStyle contentYellowStyle = PoiUtils.createContentCellStyle(workbook);
	        contentYellowStyle.setFillForegroundColor(HSSFColor.YELLOW.index);
	        contentYellowStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
	        
	        // 产生表格标题行
	        HSSFRow row = sheet.createRow(0);
	        for (short i = 0; i < headers.length; i++) {
	            HSSFCell cell = row.createCell(i);
	            cell.setCellStyle(headerStyle);
	            HSSFRichTextString text = new HSSFRichTextString(headers[i]);
	            cell.setCellValue(text);
	        }
	        
			if (dataset != null && dataset.size() > 0) {
				int index = 0;
				for (Iterator iterator = dataset.iterator(); iterator
						.hasNext();) {
					boolean isYelloBackColor = false;
					
					if(colorYellowRow.contains(index)){
						isYelloBackColor = true;
					}
					
					index ++ ;
					Object cf = iterator.next();
					row = sheet.createRow(index);
	
					 for (short i = 0; i < columns.length; i++) {
						 HSSFCell cell = row.createCell(i);
						 
						 if(isYelloBackColor){
							 cell.setCellStyle(contentYellowStyle);
						 }else{
							 cell.setCellStyle(contentStyle);
						 }
			             
						 Object obj = Ognl.getValue(columns[i], context, cf);
						 
						 String textValue = obj != null ? obj.toString() : "";
			             if (textValue != null) {
		                      Pattern p = Pattern.compile("^//d+(//.//d+)?{1}");
		                      Matcher matcher = p.matcher(textValue);
		                      if (matcher.matches()) {
		                          // 是数字当作double处理
		                          cell.setCellValue(Double.parseDouble(textValue));
		                      } else {
		                          HSSFRichTextString richString = new HSSFRichTextString(
		                                    textValue);
		                            HSSFFont font3 = PoiUtils.createTextFont(workbook);
		                            richString.applyFont(font3);
		                            cell.setCellValue(richString);
		                      }
		                     
			             }
					 }

				}

				try {
					workbook.write(out);
					out.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}

	    }	
	
	
	/**
	 * 将字幕转换成索引
	 * @param letter
	 * @return
	 */
	public static int letterToInt(String letter) {
		int n = 0;

		if (letter.trim().length() == 0)
			throw new RuntimeException("不接受空字符串！");

		if (letter.length() >= 2) {
			char c1 = letter.toCharArray()[0];
			char c2 = letter.toCharArray()[1];
			if (!Character.isLetter(c1) || !Character.isLetter(c2)) {
				throw new RuntimeException("格式不正确，必须是字母！");
			}
			c1 = Character.toUpperCase(c1);
			c2 = Character.toUpperCase(c2);

			int i = ((int) c1) - 64;
			int j = ((int) c2) - 64;
			n = i * 26 + j;
		}

		if (letter.length() == 1) {
			char c1 = letter.toCharArray()[0];
			if (!Character.isLetter(c1)) {
				throw new RuntimeException("格式不正确，必须是字母！");
			}
			c1 = Character.toUpperCase(c1);
			n = ((int) c1) - 64;
		}
		if (n > 256)
			throw new RuntimeException("索引超出范围，Excel的列索引不能超过256！");

		return n;
	}

	/**
	 * 将列的索引换算成ABCD字母，这个方法要在插入公式时用到。
	 * 
	 * @param colIndex
	 *            列索引。
	 * @return ABCD字母。
	 */
	public static String getColLetter(int colIndex) {
		String ch = "";
		if (colIndex < 26)
			ch = "" + (char) ((colIndex) + 65);
		else
			ch = "" + (char) ((colIndex) / 26 + 65 - 1)
					+ (char) ((colIndex) % 26 + 65);
		return ch;
	}
	
	
	public static void setRegionStyle(HSSFSheet sheet, CellRangeAddressBase region , HSSFCellStyle cs) {
//        int toprowNum = region.getFirstRow();
        for (int i = region.getFirstRow(); i <= region.getLastRow(); i ++) {
            HSSFRow row = HSSFCellUtil.getRow(i, sheet);
            for (int j = region.getFirstColumn(); j <= region.getLastColumn(); j++) {
                HSSFCell cell = HSSFCellUtil.getCell(row, j);
                cell.setCellStyle(cs);
            }
        }
 }
}
